﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Npgsql;

#region api_sql_run_before_use
/*
 
CREATE LANGUAGE plpgsql;

CREATE TYPE parentsChilds AS (conceptType text, conceptLevel integer, conceptParent bool);

CREATE OR REPLACE FUNCTION getparents(concepttype text)
  RETURNS SETOF parentschilds AS
$BODY$
DECLARE
 tableTypesIntertable char(30) := 'concepts_relations';
 tableTypes char(30) := 'concepts';
 tableTypes2 char(30) := tableTypes;
 searchSql text = '';
 myRow parentsChilds%rowtype;
 tmpRecord record;
BEGIN
 searchSql := 'SELECT type2.type AS tType, type2.level AS tLevel from '||tableTypesIntertable||' typeInt LEFT JOIN '||tableTypes||' type1 ON typeInt."domain"=type1."ID" LEFT JOIN '||tableTypes2||' type2 ON typeInt."range" = type2."ID" WHERE type1.type='''||conceptType||''';';
 FOR tmpRecord IN EXECUTE(searchSql) LOOP
   myRow.conceptType := tmpRecord.tType;
   myRow.conceptLevel := tmpRecord.tLevel;
   myRow.conceptParent := true;
   RETURN NEXT myRow;
  END LOOP;
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION getparents(text) OWNER TO postgres;

CREATE OR REPLACE FUNCTION getchilds(concepttype text)
  RETURNS SETOF parentschilds AS
$BODY$
DECLARE
 tableTypesIntertable char(30) := 'concepts_relations';
 tableTypes char(30) := 'concepts';
 tableTypes2 char(30) := tableTypes;
 searchSql text = '';
 myRow parentsChilds%rowtype;
 tmpRecord record;
BEGIN
 searchSql := 'SELECT type1.type AS tType, type1.level AS tLevel FROM '||tableTypesIntertable||' typeInt LEFT JOIN '||tableTypes||' type1 ON typeInt."domain"=type1."ID" LEFT JOIN '||tableTypes2||' type2 ON typeInt."range" = type2."ID" WHERE type2.type='''||conceptType||''';';
 FOR tmpRecord IN EXECUTE(searchSql) LOOP
   myRow.conceptType := tmpRecord.tType;
   myRow.conceptLevel := tmpRecord.tLevel;
   myRow.conceptParent := false;
   RETURN NEXT myRow;
  END LOOP;
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION getchilds(text) OWNER TO postgres;

 * */
#endregion

namespace TeamProject
{
    public class Utils
    {
        public static void LogError(String error)
        {
            Console.WriteLine(error);
        }
    }
}

namespace TeamProject.Concepts
{
    public class CategoryItem
    {
        private String type;
        private String[] parents;
        private int level;
        private int id;
        private int linkToNodeId; // parent or child

        public CategoryItem()
        {
        }

        public CategoryItem(String type, int level)
        {
            this.type = type;
            this.level = level;
        }

        public CategoryItem(int id, String type, int level, int linkToNodeId)
        {
            this.type = type;
            this.level = level;
            this.id = id;
            this.linkToNodeId = linkToNodeId;
        }

        public CategoryItem(String type, int level, String[] parents)
        {
            this.type = type;
            this.level = level;
            this.parents = parents;
        }

        public String Type
        {
            get { return type; }
            set { type = value; }
        }

        public int LinkToNodeId
        {
            get { return linkToNodeId; }
            set { linkToNodeId = value; }
        }

        public int Id
        {
            get { return id; }
            set { id = value; }
        }

        public int Level
        {
            get { return level; }
            set { level = value; }
        }

        public String[] Parents
        {
            get { return parents; }
            set { parents = value; }
        }
    }

    public class Categories
    {
        private static List<CategoryItem> myCategories;
        private static NpgsqlCommand command;
        private static NpgsqlDataReader dr;
        private static String tmpCompareParent = "";
        private static String tmpCompareType = "";
        private static String typeTable = "concepts";
        private static String columnTypeTableId = "ID";
        private static String columnType = "type";
        private static String columnLevel = "level";
        private static String typeInterTable = "concepts_relations";
        private static String columnParent = "range";
        private static String columnTypeId = "domain";


        public static int GetLevel(NpgsqlConnection conn, String conceptType)
        {
            int level = -1;

            try
            {
                command = new NpgsqlCommand("select * from " + typeTable + " typeTable where typeTable." + columnType + " = '" + conceptType + "';", conn);
                dr = command.ExecuteReader();

                if (dr.Read())
                {
                    level = Convert.ToInt32(dr[columnLevel]);
                }
            }
            catch (Exception e)
            {
                Utils.LogError(e.Message);
            }

            return level;
        }

        public static List<CategoryItem> GetParents(NpgsqlConnection conn, String conceptType)
        {
            myCategories = new List<CategoryItem>();
            string tmpsql = "SELECT type2.\"" + columnTypeTableId + "\", type2." + columnType + ", type2." + columnLevel + ", typeInt." + columnTypeId + " ";
            tmpsql += "FROM " + typeInterTable + " typeInt ";
            tmpsql += "LEFT JOIN " + typeTable + " type1 ON typeInt." + columnTypeId + "= type1.\"" + columnTypeTableId + "\" ";
            tmpsql += "LEFT JOIN " + typeTable + " type2 ON typeInt." + columnParent + " = type2.\"" + columnTypeTableId + "\" ";
            tmpsql += "WHERE type1." + columnType + "='" + conceptType + "';";

            try
            {
                command = new NpgsqlCommand(tmpsql, conn);
                dr = command.ExecuteReader();

                while (dr.Read())
                {
                    int _id = Convert.ToInt32(dr[columnTypeTableId]);
                    String _type = dr[columnType].ToString();
                    int _level = Convert.ToInt32(dr[columnLevel]);
                    int _child = Convert.ToInt32(dr[columnTypeId]);

                    myCategories.Add(new CategoryItem(_id, _type, _level, _child));
                }
            }
            catch (Exception e)
            {
                Utils.LogError(e.Message);
            }

            return myCategories;
        }

        public static List<CategoryItem> GetChilds(NpgsqlConnection conn, String conceptType)
        {
            myCategories = new List<CategoryItem>();
            string tmpsql = "SELECT type1.\"" + columnTypeTableId + "\", type1." + columnType + ", type1." + columnLevel + ", typeInt." + columnParent + " ";
            tmpsql += "FROM " + typeInterTable + " typeInt ";
            tmpsql += "LEFT JOIN " + typeTable + " type1 ON typeInt." + columnTypeId + "=type1.\"" + columnTypeTableId + "\" ";
            tmpsql += "LEFT JOIN " + typeTable + " type2 ON typeInt." + columnParent + " = type2.\"" + columnTypeTableId + "\" ";
            tmpsql += "WHERE type2." + columnType + "='" + conceptType + "';";

            try
            {
                command = new NpgsqlCommand(tmpsql, conn);
                dr = command.ExecuteReader();

                while (dr.Read())
                {
                    int _id = Convert.ToInt32(dr[columnTypeTableId]);
                    String _type = dr[columnType].ToString();
                    int _level = Convert.ToInt32(dr[columnLevel]);
                    int _parent = Convert.ToInt32(dr[columnParent]);

                    myCategories.Add(new CategoryItem(_id, _type, _level, _parent));
                }
            }
            catch (Exception e)
            {
                Utils.LogError(e.Message);
            }

            return myCategories;
        }

        public static List<CategoryItem> GetParentsChilds(NpgsqlConnection conn, String conceptType)
        {
            // gets parents and childs together

            myCategories = new List<CategoryItem>();
            string tmpsql = "SELECT type2.\"" + columnTypeTableId + "\", type2." + columnType + ", type2." + columnLevel + ", typeInt." + columnTypeId + " as linkTo ";
            tmpsql += "FROM " + typeInterTable + " typeInt ";
            tmpsql += "LEFT JOIN " + typeTable + " type1 ON typeInt." + columnTypeId + "= type1.\"" + columnTypeTableId + "\" ";
            tmpsql += "LEFT JOIN " + typeTable + " type2 ON typeInt." + columnParent + " = type2.\"" + columnTypeTableId + "\" ";
            tmpsql += "WHERE type1." + columnType + "='" + conceptType + "' UNION ";
            // second select
            tmpsql += "SELECT type1.\"" + columnTypeTableId + "\", type1." + columnType + ", type1." + columnLevel + ", typeInt." + columnParent + " as linkTo ";
            tmpsql += "FROM " + typeInterTable + " typeInt ";
            tmpsql += "LEFT JOIN " + typeTable + " type1 ON typeInt." + columnTypeId + "=type1.\"" + columnTypeTableId + "\" ";
            tmpsql += "LEFT JOIN " + typeTable + " type2 ON typeInt." + columnParent + " = type2.\"" + columnTypeTableId + "\" ";
            tmpsql += "WHERE type2." + columnType + "='" + conceptType + "';";

            try
            {
                command = new NpgsqlCommand(tmpsql, conn);
                dr = command.ExecuteReader();

                while (dr.Read())
                {
                    int _id = Convert.ToInt32(dr[columnTypeTableId]);
                    String _type = dr[columnType].ToString();
                    int _level = Convert.ToInt32(dr[columnLevel]);
                    int _link = Convert.ToInt32(dr["linkTo"]);

                    myCategories.Add(new CategoryItem(_id, _type, _level, _link));
                }
            }
            catch (Exception e)
            {
                Utils.LogError(e.Message);
            }

            return myCategories;
        }

        public static List<CategoryItem> GetTypeNeighbours(NpgsqlConnection conn, String conceptType)
        {
            // gets same level items , without itself

            myCategories = new List<CategoryItem>();
            String typeLevel;
            String typeParent;
            String tmpSql = "select type1.\"" + columnLevel + "\" As tLevel, typeInt.\"" + columnParent + "\" AS tParent from " + typeTable + " type1 ";
            tmpSql += "left join " + typeInterTable + " typeInt on typeInt.\"" + columnTypeId + "\" = type1.\"" + columnTypeTableId + "\" ";
            tmpSql += "where type1.\"" + columnType + "\" = '" + conceptType + "';";

            try
            {
                command = new NpgsqlCommand(tmpSql, conn);
                dr = command.ExecuteReader();

                if (dr.Read())
                {
                    typeLevel = dr["tLevel"].ToString();
                    typeParent = dr["tParent"].ToString();

                    tmpSql = "select type2.\"" + columnType + "\", type2.\"" + columnLevel + "\" from " + typeTable + " type1 ";
                    tmpSql += "left join " + typeInterTable + " typeInt on typeInt.\"" + columnParent + "\" = " + typeParent;
                    tmpSql += " left join " + typeTable + " type2 on type2.\"" + columnTypeTableId + "\" = typeInt.\"" + columnParent + "\" ";
                    tmpSql += "where type1.\"" + columnLevel + "\" = " + typeLevel + " and type1.\"" + columnType + "\" = '" + conceptType + "';";

                    dr.Close();
                    command.CommandText = tmpSql;
                    dr = command.ExecuteReader();

                    while (dr.Read())
                    {
                        //myCategories.Add(new CategoryItem(dr[0].ToString(), Convert.ToInt32(dr[1])));
                    }
                }
                else
                {
                    throw (new Exception("Could not find parent for type " + conceptType + "\nMethod: Categories.GetTypeNeighbours"));
                }
            }
            catch (Exception e)
            {
                Utils.LogError(e.Message);
            }

            return myCategories;
        }

        public static List<CategoryItem> GetTree(NpgsqlConnection conn, String conceptType, int depth)
        {
            //gets all items to depth level with beginning at conceptType

            myCategories = new List<CategoryItem>();
            int level;

            Predicate<CategoryItem> typeExist = delegate(CategoryItem ci)
            {
                return ci.Type == tmpCompareType;
            };

            try
            {
                level = Categories.GetLevel(conn, conceptType);

                // reads child items first
                command = new NpgsqlCommand("select * from GetChilds('" + conceptType + "');", conn);
                dr = command.ExecuteReader();

                while (dr.Read())
                {
                    // if depth is in range, add new item with parent set
                    if (Convert.ToInt32(dr[1]) <= depth)
                        myCategories.Add(new CategoryItem(dr[0].ToString(), Convert.ToInt32(dr[1]), new String[] { conceptType }));
                }

                // reads by level, determines and sets item's parent
                for (int i = level + 1; i <= depth; i++)
                {
                    String tmpSql = "SELECT type1.\"" + columnType + "\" AS tType, type1.\"" + columnLevel + "\" AS tLevel, type2.\"" + columnType + "\" AS tParent FROM " + typeInterTable + " typeInt ";
                    tmpSql += "LEFT JOIN " + typeTable + " type1 ON typeInt.\"" + columnTypeId + "\" = type1.\"" + columnTypeTableId + "\" ";
                    tmpSql += "LEFT JOIN " + typeTable + " type2 ON typeInt.\"" + columnParent + "\" = type2.\"" + columnTypeTableId + "\" ";
                    tmpSql += "WHERE type1.\"" + columnLevel + "\" = " + i + " ORDER BY tLevel asc;";

                    dr.Close();
                    command.CommandText = tmpSql;
                    dr = command.ExecuteReader();

                    while (dr.Read())
                    {
                        tmpCompareType = dr["tType"].ToString();
                        tmpCompareParent = dr["tParent"].ToString();

                        // check if type already exists in myCategories
                        CategoryItem ci;
                        ci = myCategories.Find(typeExist);

                        if (ci != null)
                        {
                            // if parent is not in parents array of the item 
                            if (!ci.Parents.Contains(tmpCompareParent))
                            {
                                ci.Parents = new String[] { string.Join(",", ci.Parents), tmpCompareParent };
                            }
                        }
                        else
                        {
                            myCategories.Add(new CategoryItem(tmpCompareType, Convert.ToInt32(dr["tLevel"]), new String[] { tmpCompareParent }));
                        }
                    }

                }
            }
            catch (Exception e)
            {
                Utils.LogError(e.Message);
            }

            return myCategories;
        }

        public static bool IsAChild(NpgsqlConnection conn, String originalType, String parentType)
        {
            if (originalType == parentType)
                return true;

            bool result = false;
            bool childrenExist = true;
            String[] childs = new String[1];
            String tmpSql = "";

            try
            {
                NpgsqlCommand command = new NpgsqlCommand();
                command.Connection = conn;
                childs[0] = originalType;

                while (childrenExist && result == false)
                {
                    //select childs
                    tmpSql = "SELECT type1.\"" + columnType + "\" AS tType ";
                    tmpSql += "FROM \"" + typeInterTable + "\" typeInt ";
                    tmpSql += "LEFT JOIN \"" + typeTable + "\" type1 ON typeInt.\"" + columnTypeId + "\" = type1.\"" + columnTypeTableId + "\"";
                    tmpSql += "LEFT JOIN \"" + typeTable + "\" type2 ON typeInt.\"" + columnParent + "\" = type2.\"" + columnTypeTableId + "\" WHERE ";

                    // WHERE PODMIENKA
                    foreach (String item in childs)
                    {
                        tmpSql += "type2.\"" + columnType + "\" = '" + item + "' OR ";
                    }
                    tmpSql = tmpSql.Remove(tmpSql.Length - 4);
                    Array.Resize<String>(ref childs, 0);

                    command.CommandText = tmpSql;
                    dr.Close();
                    dr = command.ExecuteReader();

                    if (dr.HasRows)
                    {
                        while (dr.Read() && result == false)
                        {
                            if (dr["tType"].ToString() == parentType)
                            {
                                result = true;
                            }
                            else
                            {
                                Array.Resize<String>(ref childs, childs.Length + 1);
                                childs[childs.Length - 1] = dr["tType"].ToString();
                            }
                        }
                    }
                    else
                    {
                        childrenExist = false;
                    }
                }//while
            }
            catch (Exception e)
            {
                Utils.LogError(e.Message);
            }

            return result;
        }

        public static bool IsAParent(NpgsqlConnection conn, String originalType, String parentType)
        {
            if (originalType == parentType)
                return true;

            bool result = false;
            bool parentsExist = true;
            String[] parents = new String[1];
            String tmpSql = "";

            try
            {
                NpgsqlCommand command = new NpgsqlCommand();
                command.Connection = conn;
                parents[0] = originalType;

                while (parentsExist && result == false)
                {
                    //select parents
                    tmpSql = "SELECT type2.\"" + columnType + "\" AS tType ";
                    tmpSql += "FROM \"" + typeInterTable + "\" typeInt ";
                    tmpSql += "LEFT JOIN \"" + typeTable + "\" type1 ON typeInt.\"" + columnTypeId + "\" = type1.\"" + columnTypeTableId + "\"";
                    tmpSql += "LEFT JOIN \"" + typeTable + "\" type2 ON typeInt.\"" + columnParent + "\" = type2.\"" + columnTypeTableId + "\" WHERE ";

                    // WHERE PODMIENKA
                    foreach (String item in parents)
                    {
                        tmpSql += "type1.\"" + columnType + "\" = '" + item + "' OR ";
                    }
                    tmpSql = tmpSql.Remove(tmpSql.Length - 4);
                    Array.Resize<String>(ref parents, 0);

                    command.CommandText = tmpSql;
                    dr.Close();
                    dr = command.ExecuteReader();

                    if (dr.HasRows)
                    {
                        while (dr.Read() && result == false)
                        {
                            if (dr["tType"].ToString() == parentType)
                            {
                                result = true;
                            }
                            else
                            {
                                Array.Resize<String>(ref parents, parents.Length + 1);
                                parents[parents.Length - 1] = dr["tType"].ToString();
                            }
                        }
                    }
                    else
                    {
                        parentsExist = false;
                    }
                }//while
            }
            catch (Exception e)
            {
                Utils.LogError(e.Message);
            }

            return result;
        }

        public static bool isA(NpgsqlConnection conn, String originalType, String parentType)
        {
            bool result = false;

            result = Categories.IsAParent(conn, originalType, parentType);

            if (!result)
            {
                result = Categories.IsAChild(conn, originalType, parentType);
            }

            return result;
        }
    }
}
